package com.tpc.server.systeme.service;

import java.sql.ResultSet;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.tpc.server.systeme.utils.DatabaseManager;
import com.tpc.server.systeme.utils.SecuriteManager;
import java.util.logging.Logger;
import com.tpc.client.systeme.core.service.AddressService;
import com.tpc.dao.extend.Address;
import com.tpc.dao.core.Country;
import com.tpc.dao.core.City;
import com.tpc.dao.core.State;
import com.tpc.dao.core.Zipcode;
import com.tpc.dao.extend.Airport;
import com.tpc.server.systeme.utils.DateManager;
import java.util.Properties;

/**
 *
 * @author frederic.fortin
 */

@SuppressWarnings("serial")
public class AddressServiceImpl extends RemoteServiceServlet implements AddressService {

    private static final long serialVersionUID = 3L;
    private final static Logger objLog = Logger.getLogger(AddressServiceImpl.class.getName());

    public AddressServiceImpl() {
        
    }

    public Address loadOrganisationAddress2(String strOrganisationId) {
        try {
            Address adresse = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Address.class.getSimpleName(),"READ")) {
                DatabaseManager objBD = objSecurite.getDataBaseManager();
                Properties objProperties = objSecurite.getProperties();
                String sqlAddress2 = "SELECT address.id,address.street,address.suite,address_zipcode.zipcode,address_zipcode.id,address_city.name,address_state.name,address_country.name,"
                            + "FROM organisation_has_address,address,address_zipcode,address_city,address_state,address_country "
                            + "WHERE organisation_has_address.organisationId = ? AND organisation_has_address.addressId = address.id AND address.zipcodeId = address_zipcode.id AND "
                            + "address_zipcode.addressCityId = address_city.id AND address_city.addressStateId = address_state.id AND address_state.countryId = address_country.id AND "
                            + "address.defaut = ? AND address.life = ?";
                String[] params = {strOrganisationId,objProperties.getProperty("DEFAULT"),objProperties.getProperty("ENABLE")};

                ResultSet objRS = objBD.executeQuery(sqlAddress2,params);
                if (objRS != null) {
                    if (objRS.next()) {
                        adresse = new Address();
                        adresse.setId(objRS.getInt("address.id"));
                        adresse.setStreet("address.street");
                        adresse.setCityName(objRS.getString("address_city.name"));
                        adresse.setDistrictName(objRS.getString(""));
                        adresse.setCountryName(objRS.getString("address_country.name"));
                        adresse.setStateName(objRS.getString("address_state.name"));
                        adresse.setZipcode(objRS.getString("address_zipcode.zipcode"));
                        adresse.setZipcodeId(objRS.getInt("address_zipcode.id"));
                        adresse.setOrganisation();
                        adresse.getOrganisation().setId(new Integer(strOrganisationId).intValue());
                    }
                }
                objBD.closeConnection();
            }
            return adresse;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }

    public int createAddressOrganisation(Address adresse) {
        try {
            int success = 0;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Address.class.getSimpleName(),"EXEC")) {
                DateManager dateM = new DateManager();
                if (adresse != null) {
                    String sql = "INSERT INTO address SET street = ?,defaut = ?,zipcodeId = ?,life = ?,created_date = ?,created_by = ?,modified_date = ?,modified_by = ?,TYPE = ?";
                    String[] params = {adresse.getStreet(),objSecurite.getProperties().getProperty("NOT_DEFAULT"),new Integer(adresse.getZipcodeId()).toString(),objSecurite.getProperties().getProperty("ENABLE"),dateM.NOW(),new Integer(objSecurite.getUser().getId()).toString(),dateM.NOW(),new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("TYPE_ADDRESS")};
                    success = objSecurite.getDataBaseManager().executeUpdate(sql,params);

                    int adresseId = objSecurite.getDataBaseManager().getLastInsertedId();
                    // BIND to Organisation
                    String sqlOrg = "INSERT INTO organisation_has_address SET organisationId = ?, addressId = ?";
                    String[] paramOrg = {new Integer(adresse.getOrganisation().getId()).toString(),new Integer(adresseId).toString()};
                    success = objSecurite.getDataBaseManager().executeUpdate(sqlOrg,paramOrg);
                }
            }
            return success;
        }
        catch(Exception e) {
            objLog.info("message 2: "+e.getMessage());
            return -2;
        }
    }

    public int createAddressContact(Address adresse) {
        try {
            int success = 0;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Address.class.getSimpleName(),"EXEC")) {
                DateManager dateM = new DateManager();
                if (adresse != null) {
                    String sql = "INSERT INTO address SET street = ?,defaut = ?,zipcodeId = ?,life = ?,created_date = ?,created_by = ?,modified_date = ?,modified_by = ?,TYPE = ?";
                    String[] params = {adresse.getStreet(),objSecurite.getProperties().getProperty("NOT_DEFAULT"),new Integer(adresse.getZipcodeId()).toString(),objSecurite.getProperties().getProperty("ENABLE"),dateM.NOW(),new Integer(objSecurite.getUser().getId()).toString(),dateM.NOW(),new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("TYPE_ADDRESS")};
                    success = objSecurite.getDataBaseManager().executeUpdate(sql,params);

                    int adresseId = objSecurite.getDataBaseManager().getLastInsertedId();
                    // BIND to Organisation
                    String sqlOrg = "INSERT INTO user_has_address SET user_id = ?, address_id = ?";
                    String[] paramOrg = {new Integer(adresse.getUser().getId()).toString(),new Integer(adresseId).toString()};
                    success = objSecurite.getDataBaseManager().executeUpdate(sqlOrg,paramOrg);
                }
            }
            return success;
        }
        catch(Exception e) {
            objLog.info("message 2: "+e.getMessage());
            return -2;
        }
    }

    public Country[] loadListCountry() {
        try {
            Country[] arrobjCountry = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Address.class.getSimpleName(),"READ")) {
                DatabaseManager objBD = objSecurite.getDataBaseManager();
                Properties objProperties = objSecurite.getProperties();
                /** @TODO : should get parameter (life = ENABLE) from properties inside session  **/
                String[] arrstrParameters = {objProperties.getProperty("ENABLE")};
                String strQueryLoadListCountry = "SELECT id,name,code FROM address_country WHERE life = ? ORDER BY name";
                ResultSet objRS = objBD.executeQuery(strQueryLoadListCountry, arrstrParameters);
                if (objRS != null) {
                    int i = 0;
                    int intNbrRow = 0;
                    objRS.last();
                    intNbrRow = objRS.getRow();
                    arrobjCountry = new Country[intNbrRow];
                    objRS.beforeFirst();
                    while (objRS.next()) {
                        arrobjCountry[i] = new Country();
                        arrobjCountry[i].setId(objRS.getInt("id"));
                        arrobjCountry[i].setName(objRS.getString("name"));
                        arrobjCountry[i].setCode(objRS.getString("code"));
                        i++;
                    }
                }
                objBD.closeConnection();
            }
            return arrobjCountry;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }

    public State[] loadListState(String param) {
        try {
            State[] arrobjState = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Address.class.getSimpleName(),"READ")) {
                DatabaseManager objBD = objSecurite.getDataBaseManager();
                String sqlState = "SELECT address_state.id AS STATEID,address_state.name AS STATENAME,address_country.id AS COUNTRYID,address_country.name AS COUNTRYNAME FROM address_state,"
                            + "address_country WHERE address_state.countryId = address_country.id AND (address_state.countryId = 38 OR address_state.countryId = 138 OR address_state.countryId = 223)";
                if (param != null ) {
                    sqlState = "SELECT address_state.id AS STATEID,address_state.name AS STATENAME,address_country.id AS COUNTRYID,address_country.name AS COUNTRYNAME FROM address_state,"
                    + "address_country WHERE address_state.name LIKE '%"+param+"%' AND address_state.countryId = address_country.id ORDER BY STATENAME LIMIT 0,20";
                }
                ResultSet objRS = objBD.executeQuery(sqlState,null);
                if (objRS != null) {
                    int i = 0;
                    int intNbrRow = 0;
                    objRS.last();
                    intNbrRow = objRS.getRow();
                    arrobjState = new State[intNbrRow];
                    objRS.beforeFirst();
                    while (objRS.next()) {
                        arrobjState[i] = new State();
                        arrobjState[i].setId(objRS.getInt("STATEID"));
                        arrobjState[i].setName(objRS.getString("STATENAME"));
                        arrobjState[i].setCountryId(objRS.getInt("COUNTRYID"));
                        arrobjState[i].setCountryName(objRS.getString("COUNTRYNAME"));
                        i++;
                    }
                }
                objBD.closeConnection();
            }
            return arrobjState;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }

    public City[] loadListCity(String param,String param2) {
        try {
            City[] arrobjCity = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Address.class.getSimpleName(),"READ")) {
                DatabaseManager objBD = objSecurite.getDataBaseManager();
                String sqlCity = "";
                if (param2 != null) {
                    sqlCity = "SELECT address_city.id AS CITYID,address_city.name AS CITYNAME,address_state.id AS STATEID,address_state.name AS STATENAME,address_country.id AS COUNTRYID,address_country.name AS COUNTRYNAME FROM address_city,address_state,address_country WHERE address_city.addressStateId = address_state.id AND address_state.countryId = address_country.id AND address_city.id = '"+param2+"' ORDER BY address_city.name LIMIT 0,20";
                }
                else {
                    if (param == null) {
                        param = "a";
                    }
                    sqlCity = "SELECT address_city.id AS CITYID,address_city.name AS CITYNAME,address_state.id AS STATEID,address_state.name AS STATENAME,address_country.id AS COUNTRYID,address_country.name AS COUNTRYNAME FROM address_city,address_state,address_country WHERE address_city.addressStateId = address_state.id AND address_state.countryId = address_country.id AND address_city.name LIKE '"+param+"%' ORDER BY address_city.name LIMIT 0,20";
                }
                ResultSet objRS = objBD.executeQuery(sqlCity, null);
                if (objRS != null) {
                    int i = 0;
                    int intNbrRow = 0;
                    objRS.last();
                    intNbrRow = objRS.getRow();
                    arrobjCity = new City[intNbrRow];
                    objRS.beforeFirst();
                    while (objRS.next()) {
                        arrobjCity[i] = new City();
                        arrobjCity[i].setId(objRS.getInt("CITYID"));
                        arrobjCity[i].setName(objRS.getString("CITYNAME"));
                        arrobjCity[i].setStateId(objRS.getInt("STATEID"));
                        arrobjCity[i].setStateName(objRS.getString("STATENAME"));
                        arrobjCity[i].setCountryId(objRS.getInt("COUNTRYID"));
                        arrobjCity[i].setCountryName(objRS.getString("COUNTRYNAME"));
                        i++;
                    }
                }
                objBD.closeConnection();
            }
            return arrobjCity;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }

    public City[] loadListAiport(String cityTerm,String organisationId,String transportId) {
        try {
            City[] arrobjCity = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Address.class.getSimpleName(),"READ")) {
                DatabaseManager objBD = objSecurite.getDataBaseManager();
                Properties objProperties = objSecurite.getProperties();
                String dateNOW = new DateManager().NOW();

                if (transportId.equalsIgnoreCase(objProperties.getProperty("TRANSPORT_PLANE"))) {
                    String sqlAirport = "SELECT address_city.id AS CITYID,address_city.name AS CITYNAME,address_state.id AS STATEID,address_state.name AS STATENAME,address_country.id AS COUNTRYID,address_country.name AS "
                            + "COUNTRYNAME FROM airport,address_city,address_state,address_country WHERE address_city.name LIKE '%"+cityTerm+"%' AND airport.cityId = address_city.id AND address_city.addressStateId = address_state.id "
                            + "AND address_state.countryId = address_country.id ORDER BY address_city.name LIMIT 0,100";
                    ResultSet objRS = objBD.executeQuery(sqlAirport,null);
                    if (objRS != null) {
                        int i = 0;
                        int intNbrRow = 0;
                        objRS.last();
                        intNbrRow = objRS.getRow();
                        arrobjCity = new City[intNbrRow];
                        objRS.beforeFirst();
                        while (objRS.next()) {
                            arrobjCity[i] = new City();
                            arrobjCity[i].setId(objRS.getInt("CITYID"));
                            arrobjCity[i].setName(objRS.getString("CITYNAME"));
                            arrobjCity[i].setStateId(objRS.getInt("STATEID"));
                            arrobjCity[i].setStateName(objRS.getString("STATENAME"));
                            arrobjCity[i].setCountryId(objRS.getInt("COUNTRYID"));
                            arrobjCity[i].setCountryName(objRS.getString("COUNTRYNAME"));
                            i++;
                        }
                    }
                }
                else {
                    String sqlBusCity = "SELECT address_city.id AS CITYID,address_city.name AS CITYNAME,address_state.id AS STATEID,address_state.name AS STATENAME,address_country.id AS COUNTRYID,address_country.name AS "
                            + "COUNTRYNAME FROM organisation_has_address,address,address_zipcode,address_city,address_state,address_country WHERE organisationId = ? AND addressId = address.id AND address.zipcodeId = "
                            + "address_zipcode.id AND address_zipcode.addressCityId = address_city.id AND address_city.addressStateId = address_state.id AND address_state.countryId = address_country.id ORDER BY "
                            + "address_city.name LIMIT 0,100";
                    String[] parameter = {organisationId};
                    ResultSet objRS = objBD.executeQuery(sqlBusCity,parameter);
                    if (objRS != null) {
                        int i = 0;
                        int intNbrRow = 0;
                        objRS.last();
                        intNbrRow = objRS.getRow();
                        arrobjCity = new City[intNbrRow];
                        objRS.beforeFirst();
                        while (objRS.next()) {
                            arrobjCity[i] = new City();
                            arrobjCity[i].setId(objRS.getInt("CITYID"));
                            arrobjCity[i].setName(objRS.getString("CITYNAME"));
                            arrobjCity[i].setStateId(objRS.getInt("STATEID"));
                            arrobjCity[i].setStateName(objRS.getString("STATENAME"));
                            arrobjCity[i].setCountryId(objRS.getInt("COUNTRYID"));
                            arrobjCity[i].setCountryName(objRS.getString("COUNTRYNAME"));
                            i++;
                        }
                    }
                }
                objBD.closeConnection();
            }
            return arrobjCity;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }
 
    public Airport[] loadListAiportForOrganisation(String airportName) {
        try {
            Airport[] airports = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Address.class.getSimpleName(),"READ")) {
                DatabaseManager objBD = objSecurite.getDataBaseManager();

                String sqlAirport = "SELECT airport.id,airport.name,airport.code,address_city.name,address_state.name,address_country.name FROM airport,address_city,address_state,address_country "
                        + "WHERE airport.name LIKE '%"+airportName+"%' AND airport.cityId = address_city.id AND address_city.addressStateId = address_state.id AND address_state.countryId = address_country.id "
                        + "ORDER BY airport.name LIMIT 0,10";
                ResultSet objRS = objBD.executeQuery(sqlAirport,null);
                if (objRS != null) {
                    int i = 0;
                    int intNbrRow = 0;
                    objRS.last();
                    intNbrRow = objRS.getRow();
                    airports = new Airport[intNbrRow];
                    objRS.beforeFirst();
                    while (objRS.next()) {
                        airports[i] = new Airport();
                        airports[i].setId(objRS.getInt("airport.id"));
                        airports[i].setName(objRS.getString("airport.name"));
                        airports[i].setCode(objRS.getString("airport.code"));
                        airports[i].setStateName(objRS.getString("address_state.name"));
                        airports[i].setCountryName(objRS.getString("address_country.name"));
                        i++;
                    }
                }
                objBD.closeConnection();
            }
                
            return airports;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }

    public Zipcode[] loadListZipcode(String strZipcodeTerm,String strZipcodeCountryTerm) {
        try {
            Zipcode[] arrobjZipcode = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Address.class.getSimpleName(),"READ")) {
                DatabaseManager objBD = objSecurite.getDataBaseManager();
                String sqlZipcode = "SELECT address_zipcode.id,address_zipcode.zipcode,address_zipcode.addressCityId,address_city.name AS CITYNAME,address_state.id AS STATEID,address_state.name STATENAME,address_country.id COUNTRYID,address_country.name COUNTRYNAME "
                        + "FROM address_zipcode,address_city,address_state,address_country WHERE address_zipcode.addressCityId = address_city.id AND "
                        + "address_city.addressStateId = address_state.id AND address_state.countryId = address_country.id AND address_zipcode.zipcode LIKE '"+strZipcodeTerm+"%' ORDER BY address_zipcode.zipcode LIMIT 0,10";

                ResultSet objRS = objBD.executeQuery(sqlZipcode, null);
                if (objRS != null) {
                    int i = 0;
                    int intNbrRow = 0;
                    objRS.last();
                    intNbrRow = objRS.getRow();
                    arrobjZipcode = new Zipcode[intNbrRow];
                    objRS.beforeFirst();
                    while (objRS.next()) {
                        arrobjZipcode[i] = new Zipcode();
                        arrobjZipcode[i].setId(objRS.getInt("id"));
                        arrobjZipcode[i].setName(objRS.getString("zipcode"));
                        arrobjZipcode[i].setCityId(objRS.getInt("addressCityId"));
                        arrobjZipcode[i].setCityName(objRS.getString("CITYNAME"));
                        arrobjZipcode[i].setStateId(objRS.getInt("STATEID"));
                        arrobjZipcode[i].setStateName(objRS.getString("STATENAME"));
                        arrobjZipcode[i].setCountryId(objRS.getInt("COUNTRYID"));
                        arrobjZipcode[i].setCountryName(objRS.getString("COUNTRYNAME"));
                        i++;
                    }
                }
                objBD.closeConnection();
            }
            return arrobjZipcode;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }


}
